package com.mrd.shiptask_shipowner.constants.database;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author winaya
 */
public class BizDBAdapter {

	private static final String DATABASE_NAME = "shipowner.db";
	private static final int DATABASE_VERSION = 3;
	// 船源信息数据表
	private static final String SHIP_ORDER_TABLE = "CREATE TABLE SHIP_ORDER_TABLE( ID VARCHAR2(50),SHIP_ID VARCHAR2(50),"
			+ " STATUS VARCHAR2(50),"
			+ " EMPTY_SHIP_PORT VARCHAR2(50),"
			+ " ENPTY_SHIP_DATE VARCHAR2(50),"
			+ " GOODS_COUNT_UNIT VARCHAR2(50),"
			+ " GOODS_TYPE VARCHAR2(50),"
			+ " START_PORT VARCHAR2(50),"
			+ " END_PORT VARCHAR2(50),"
			+ " VALID VARCHAR2(50),"
			+ " YANQI VARCHAR2(50),"
			+ " EFFECTIVE_DATE VARCHAR2(20),"
			+ " MIN_PICE VARCHAR2(50),"
			+ " MAX_PICE VARCHAR2(50))";
	// 港口信息数据表
	private static final String PORTS = "CREATE TABLE PORTS( PINGYIN VARCHAR2(50),PORT_NAME VARCHAR2(50))";
	// 货运申请数据表
	// id
	// 截止日期loadTime
	// 装载要求loadRequire
	// 子订单号code
	// 货物cargoName
	// 起始港 startPort
	// 目的港endPort
	// 货物重量rAmount
	// 货物单位cargoUnits
	// 运价是否含港建费includePCF
	// 计费方式accountMethod
	// 包装方式packingType
	// 货主consignorCompany
	// 联系人consignor
	// 联系方式contact
	// 单价expectPrice
	// 运价是否含税票fareTax
	// 耗损率lossRate
	// 装卸船时间合计loadUseTime
	// 滞船费demurrage
	// 船舶吨位要求shipTonnages
	// 备注remark
	private static final String MAKE_TO_ORDER_TABLE = "CREATE TABLE MAKE_TO_ORDER_TABLE( ID VARCHAR2(50),LOAD_TIME VARCHAR2(50),"
			+ " LOAD_REQUIRE VARCHAR2(50),"
			+ " CODE VARCHAR2(50),"
			+ " CARGO_NAME VARCHAR2(50),"
			+ " START_PORT VARCHAR2(50),"
			+ " END_PORT VARCHAR2(50),"
			+ " R_AMOUNT VARCHAR2(50),"
			+ " CARGO_UNITS VARCHAR2(50),"
			+ " INCLUDE_PCF VARCHAR2(50),"
			+ " ACCOUNT_METHOD VARCHAR2(50),"
			+ " PACKING_TYPE VARCHAR2(50),"
			+ " CONSIGNOR_COMPANY VARCHAR2(50),"
			+ " CONSIGNOR VARCHAR2(50),"
			+ " CONTACT VARCHAR2(50),"
			+ " EXPECT_PRICE VARCHAR2(50),"
			+ " FARE_TAX VARCHAR2(50),"
			+ " LOSS_RATE VARCHAR2(50),"
			+ " LOAD_USE_TIME VARCHAR2(50),"
			+ " DEMURRAGE VARCHAR2(50),"
			+ " SHIP_TONNAGES VARCHAR2(50)," + " REMARK VARCHAR2(50))";
	// 运输合同数据表ElECTRON_MANIFEST_LIST_URL
	private static final String ElECTRON_MANIFEST_TABLE = "CREATE TABLE ElECTRON_MANIFEST_TABLE( ID VARCHAR2(50),CODE VARCHAR2(50),"
			+ " SHIPPER VARCHAR2(50),"
			+ " GOODS VARCHAR2(50),"
			+ " AMOUNT VARCHAR2(50),"
			+ " END_PORT VARCHAR2(50),"
			+ " START_PORT VARCHAR2(50),"
			+ " CLOSSING_DATE VARCHAR2(50),"
			+ " SHIPPER_CONFIRM VARCHAR2(50)," + " OWNER_CONFIRM VARCHAR2(50))";
	// 确认签单数据表
	private static final String CONFIRM_ORDER_TABLE = "CREATE TABLE CONFIRM_ORDER_TABLE( ID VARCHAR2(50),CODE VARCHAR2(50),"
			+ " SHIPPER VARCHAR2(50),"
			+ " GOODS VARCHAR2(50),"
			+ " GOODS_COUNT VARCHAR2(50),"
			+ " GOODS_UNIT VARCHAR2(50),"
			+ " CLOSSING_DATE VARCHAR2(50),"
			+ " LOADING_PORT VARCHAR2(50),"
			+ " DISCHARGE_PORT VARCHAR2(50),"
			+ " LOADING_COUNT VARCHAR2(50),"
			+ " FINISH VARCHAR2(50),"
			+ " SHIPPER_CONFIRM_AMOUNT VARCHAR2(50),"
			+ " DISCHARGE_COUNT VARCHAR2(50), SHIPPER_CONFIRM VARCHAR2(50))";
	private final Context context;
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public BizDBAdapter(Context ctx) {
		this.context = ctx;
		setDBHelper(new DatabaseHelper(context));
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.execSQL(SHIP_ORDER_TABLE);
			} catch (SQLException e) {
			}
			try {
				db.execSQL(PORTS);
			} catch (SQLException e) {
			}
			try {
				db.execSQL(MAKE_TO_ORDER_TABLE);
			} catch (SQLException e) {
			}
			try {
				db.execSQL(ElECTRON_MANIFEST_TABLE);
			} catch (SQLException e) {
			}
			try {
				db.execSQL(CONFIRM_ORDER_TABLE);
			} catch (SQLException e) {
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			if (newVersion > oldVersion) {
				switch (newVersion) {
				case 2:
					try {
						db.execSQL("DROP TABLE SHIP_ORDER_TABLE");
						onCreate(db);
					} catch (Exception e) {
						// TODO: handle exception
					}
					break;
				case 3:
					try {
						db.execSQL("DROP TABLE CONFIRM_ORDER_TABLE");
						db.execSQL(CONFIRM_ORDER_TABLE);
					} catch (Exception e) {
						// TODO: handle exception
					}
					break;
				default:
					break;
				}
			}
		}
	}

	private BizDBAdapter open() {
		db = getDBHelper().getWritableDatabase();
		return this;
	}

	public void close() {
		db.close();
		getDBHelper().close();
	}

	/**
	 * 
	 * @return
	 */
	public SQLiteDatabase openDBConnection() {
		open();
		return db;
	}

	public DatabaseHelper getDBHelper() {
		return DBHelper;
	}

	public void setDBHelper(DatabaseHelper dBHelper) {
		DBHelper = dBHelper;
	}

	public Context getContext() {
		return context;
	}

}
